package zjh.msg.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import zjh.msg.entity.Msg;
import zjh.msg.entity.vo.MsgVo;
import zjh.msg.utils.JdbcUtilsV1;
import zjh.msg.utils.Page;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;

public class MsgDao {
    public Page<MsgVo> listMsgPage(int pageNo, int pageSize) {
        try {
            Connection connection = JdbcUtilsV1.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select id, nickname, qq, email, `text`, reply_id as replyId, create_time as createTime from `msg` where reply_id is null order by create_time desc limit ?,? ";
            List<MsgVo> msgVos = queryRunner.query(connection, sql, new BeanListHandler<MsgVo>(MsgVo.class), (pageNo-1) * pageSize, pageSize);

//           查询回复
            String replySql = "select id, nickname, qq, email, `text`, reply_id as replyId, create_time as createTime from `msg` where reply_id = ? order by create_time";
            for (MsgVo msgVo : msgVos) {
                List<Msg> replyList = queryRunner.query(connection, replySql, new BeanListHandler<Msg>(Msg.class), msgVo.getId());
                msgVo.setReplyList(replyList);
            }
//            查询count
            String countSql = "select count(*) from `msg` where reply_id is null";
            long count = queryRunner.query(connection, countSql, new ScalarHandler<Long>());
            Page<MsgVo> msgVoPage = new Page<>(pageNo, pageSize, (int) count, msgVos);
            JdbcUtilsV1.close();

            return msgVoPage;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void insertMsg(String nickname, String text, String qq, String email, String replyId) {
        try {
            Connection connection = JdbcUtilsV1.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String insertSql = "INSERT INTO `msg` (nickname, qq, `text`, email, reply_id, create_time) VALUES (?, ?, ?, ?, ?, ?)";
            int update = queryRunner.update(connection, insertSql, nickname, qq, text, email, replyId, LocalDateTime.now());
            JdbcUtilsV1.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    public void deleteMsg(int id) {
        try {
            Connection connection = JdbcUtilsV1.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String deleteSql = "delete from `msg` where id = ?";
            String deleteReplySql = "delete from `msg` where reply_id = ?";
            queryRunner.update(connection, deleteSql, id);
            queryRunner.update(connection, deleteReplySql, id);
            JdbcUtilsV1.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
